USE [WarehouseManagement]
GO
/****** Object:  StoredProcedure [dbo].[UpdateProduct]    Script Date: 04/21/2011 10:15:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		duylv00360
-- Create date: 3/14/2011
-- Description:	This stored procedure use to update a material
-- =============================================
ALTER PROCEDURE [dbo].[UpdateProduct] 
	-- Add the parameters for the stored procedure here
	@productID int = 0,
	@productCode varchar(10) = null,
	@productName nvarchar(50) = null, 
	@Description nvarchar(MAX) = null,
	@IsDelete bit = 0
	, @BuiltupStrutures NVARCHAR(MAX) = NULL
	--, @ProductID int = 0 OUT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.

    -- Insert statements for procedure here
	UPDATE Product
	SET ProductCode = @productCode
	,ProductName = @productName
	,Description = @Description
	,IsDelete = @IsDelete
	WHERE Product.ProductID = @productID
	
	IF(EXISTS (	SELECT 1 FROM Product 
				WHERE 
						ProductID = @ProductID 
						AND ProductCode = @ProductCode
						AND ProductName = @productName 
						AND [Description] = @Description
						AND IsDelete = @IsDelete))
	BEGIN
	
		DELETE FROM BuiltupStructure
		WHERE ProductID = @productID
		
		DECLARE @XmlPointer INT
	 
		EXEC sp_xml_preparedocument @XmlPointer OUTPUT, @BuiltupStrutures

		INSERT INTO BuiltupStructure(ProductID, MaterialID, MaterialQuantity)
		SELECT 
				@ProductID
				, Data.MaterialID
				, Data.Quantity
		FROM OpenXml(@XmlPointer, 'Data/Item', 2)
		WITH (MaterialID INT, Quantity INT) AS Data
		
		INNER JOIN
				Material m
		ON 
				m.MaterialID = Data.MaterialID
				
		EXEC sp_xml_removedocument @XmlPointer
	END
END

